package com.proactive.webapp.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import com.mysql.jdbc.Statement;
import com.proactive.webapp.dao.WorksheetDAO;
import com.proactive.webapp.entity.Member;
import com.proactive.webapp.entity.Worksheet;
import com.proactive.webapp.util.DateConversion;
import com.proactive.webapp.util.EncodeHtml;

public class WorksheetDAOImpl implements WorksheetDAO{
	DateConversion dateConversion = new DateConversion();
	EncodeHtml encodeHtml = new EncodeHtml();
	private DataSource dataSource;
	private Connection conn = null ;
	private ResultSet rs = null ;
	private PreparedStatement smt = null ;
	
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public int insert(Worksheet worksheet) {
		// TODO Auto-generated method stub
		String sql = "INSERT INTO worksheet(worksheetCategoryID, worksheetTitle, worksheetContent, "
				+ "memberAccount, worksheetStatus, createDate, updateDate) VALUES(?,?,?,?,?,NOW(),NOW())";
		int worksheetID = 0; 
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			smt.setInt(1, worksheet.getCategoryID());
			smt.setString(2, worksheet.getTitle());
			smt.setString(3, worksheet.getContent());
			smt.setString(4, worksheet.getAccount());
			smt.setInt(5, worksheet.getStatus());
			smt.executeUpdate();
			rs = smt.getGeneratedKeys();
			if(rs.next()){
				worksheetID = rs.getInt(1);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return worksheetID;
	}

	@Override
	public void update(Worksheet worksheet) {
		// TODO Auto-generated method stub
		String sql = "UPDATE worksheet SET worksheetCategoryID=?, worksheetTitle=?, "
				+ "worksheetContent=?, updateDate=NOW() WHERE worksheetID=?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, worksheet.getCategoryID());
			smt.setString(2, worksheet.getTitle());
			smt.setString(3, worksheet.getContent());
			smt.setInt(4, worksheet.getId());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	public void updateStatus(Worksheet worksheet) {
		// TODO Auto-generated method stub
		String sql = "UPDATE worksheet SET worksheetStatus = ? WHERE worksheetID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, worksheet.getStatus());
			smt.setInt(2, worksheet.getId());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	public void updateViewCount(Worksheet worksheet) {
		// TODO Auto-generated method stub
		String sql = "UPDATE worksheet SET worksheetViewCount = ? WHERE worksheetID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, worksheet.getViewCount());
			smt.setInt(2, worksheet.getId());
			smt.executeUpdate();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
	}

	public Worksheet get(Worksheet worksheet) {
		// TODO Auto-generated method stub
		String sql = "SELECT *,(SELECT COUNT(*) FROM worksheet_comment b WHERE topCommentID IS NULL AND a.worksheetID = b.worksheetID) "
				+ "AS commentCount,(SELECT COUNT(*) FROM worksheet_favorite c WHERE a.worksheetID = c.worksheetID) "
				+ "AS favoriteCount,(SELECT COUNT(*) FROM worksheet_report d WHERE a.worksheetID = d.worksheetID) "
				+ "AS reportCount FROM worksheet a JOIN worksheet_category e ON a.worksheetCategoryID = e.worksheetCategoryID "
				+ "JOIN member f ON a.memberAccount = f.memberAccount WHERE worksheetID = ? HAVING reportCount < 10";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, worksheet.getId());
			rs = smt.executeQuery();
			if(rs.next()){
				Member member = new Member();
				member.setAccount(rs.getString("f.memberAccount"));
				member.setName(rs.getString("f.memberName"));
				member.setJob(rs.getString("f.memberJob"));
				member.setMemberNO(rs.getString("f.memberNO"));
				
				worksheet.setMember(member);
				worksheet.setCategoryName(rs.getString("worksheetCategoryName"));
				worksheet.setCategoryID(rs.getInt("worksheetCategoryID"));
				worksheet.setTitle(rs.getString("worksheetTitle"));
				worksheet.setContent(rs.getString("worksheetContent").replaceAll("(\r\n|\n)", "<br>"));
				worksheet.setViewCount(rs.getInt("worksheetViewCount"));
				worksheet.setFavoriteCount(rs.getInt("favoriteCount"));
				worksheet.setCommentCount(rs.getInt("commentCount"));
				worksheet.setAccount(rs.getString("memberAccount"));
				worksheet.setStatus(rs.getInt("worksheetStatus"));
				worksheet.setCreateDate(rs.getDate("createDate"));
				worksheet.setCreateDateString(dateConversion.ConversionYDM(worksheet.getCreateDate()));
				worksheet.setCategoryUrl("worksheet?c1="+worksheet.getCategoryID());
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return worksheet;
	}
	
	public Worksheet getByWriter(Worksheet worksheet) {
		// TODO Auto-generated method stub
		String sql = "SELECT *,(SELECT COUNT(*) FROM worksheet_comment b WHERE topCommentID IS NULL AND a.worksheetID = b.worksheetID) "
				+ "AS commentCount,(SELECT COUNT(*) FROM worksheet_favorite c WHERE a.worksheetID = c.worksheetID) "
				+ "AS favoriteCount,(SELECT COUNT(*) FROM worksheet_report d WHERE a.worksheetID = d.worksheetID) "
				+ "AS reportCount FROM worksheet a JOIN worksheet_category e ON a.worksheetCategoryID = e.worksheetCategoryID "
				+ "JOIN member f ON a.memberAccount = f.memberAccount "
				+ "WHERE worksheetID = ?";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, worksheet.getId());
			rs = smt.executeQuery();
			if(rs.next()){
				Member member = new Member();
				member.setAccount(rs.getString("memberAccount"));
				member.setName(rs.getString("memberName"));
				member.setJob(rs.getString("memberJob"));
				member.setMemberNO(rs.getString("memberNO"));
				worksheet.setMember(member);
				worksheet.setCategoryName(rs.getString("worksheetCategoryName"));
				worksheet.setCategoryID(rs.getInt("worksheetCategoryID"));
				worksheet.setTitle(rs.getString("worksheetTitle"));
				worksheet.setContent(rs.getString("worksheetContent"));
				worksheet.setViewCount(rs.getInt("worksheetViewCount"));
				worksheet.setFavoriteCount(rs.getInt("favoriteCount"));
				worksheet.setCommentCount(rs.getInt("commentCount"));
				worksheet.setAccount(rs.getString("memberAccount"));
				worksheet.setStatus(rs.getInt("worksheetStatus"));
				worksheet.setCreateDate(rs.getDate("createDate"));
				worksheet.setCreateDateString(dateConversion.ConversionYDM(worksheet.getCreateDate()));
				worksheet.setCategoryUrl("worksheet?c1="+worksheet.getCategoryID());
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return worksheet;
	}
	

	public Worksheet getByLogin(Worksheet worksheet, String loginAccount) {
		// TODO Auto-generated method stub
		String sql = "SELECT *,(SELECT COUNT(*) FROM worksheet_comment b WHERE topCommentID IS NULL AND a.worksheetID = b.worksheetID) "
				+ "AS commentCount,(SELECT COUNT(*) FROM worksheet_favorite c WHERE a.worksheetID = c.worksheetID) "
				+ "AS favoriteCount,(SELECT COUNT(*) FROM worksheet_report d WHERE a.worksheetID = d.worksheetID) "
				+ "AS reportCount,(SELECT COUNT(*) FROM worksheet_favorite e WHERE a.worksheetID = e.worksheetID AND e.memberAccount=?) "
				+ "AS favorite,(SELECT COUNT(*) FROM worksheet_collect f WHERE a.worksheetID = f.worksheetID AND f.memberAccount=?) "
				+ "AS collect,(SELECT COUNT(*) FROM worksheet_report g WHERE a.worksheetID = g.worksheetID AND g.memberAccount=?) "
				+ "AS report FROM worksheet a JOIN worksheet_category h ON a.worksheetCategoryID = h.worksheetCategoryID "
				+ "JOIN member i ON a.memberAccount = i.memberAccount "
				+ "WHERE worksheetID = ? HAVING reportCOunt < 10";
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, loginAccount);
			smt.setString(2, loginAccount);
			smt.setString(3, loginAccount);
			smt.setInt(4, worksheet.getId());
			rs = smt.executeQuery();
			if(rs.next()){
				Member member = new Member();
				member.setAccount(rs.getString("i.memberAccount"));
				member.setName(rs.getString("i.memberName"));
				member.setJob(rs.getString("i.memberJob"));
				member.setMemberNO(rs.getString("i.memberNO"));
				worksheet.setMember(member);
				worksheet.setCategoryName(rs.getString("worksheetCategoryName"));
				worksheet.setCategoryID(rs.getInt("worksheetCategoryID"));
				worksheet.setTitle(rs.getString("worksheetTitle"));
				worksheet.setContent(rs.getString("worksheetContent").replaceAll("(\r\n|\n)", "<br>"));
				worksheet.setViewCount(rs.getInt("worksheetViewCount"));
				worksheet.setFavoriteCount(rs.getInt("favoriteCount"));
				worksheet.setCommentCount(rs.getInt("commentCount"));
				worksheet.setAccount(rs.getString("memberAccount"));
				worksheet.setStatus(rs.getInt("worksheetStatus"));
				worksheet.setCreateDate(rs.getDate("createDate"));
				worksheet.setCreateDateString(dateConversion.ConversionYDM(worksheet.getCreateDate()));
				worksheet.setCategoryUrl("worksheet?c1="+worksheet.getCategoryID());
				worksheet.setCollectFlag(rs.getInt("collect")==0?false:true);
				worksheet.setFavoriteFlag(rs.getInt("favorite")==0?false:true);
				worksheet.setReportFlag(rs.getInt("report")==0?false:true);
				worksheet.setWriterFlag(member.getAccount().equals(loginAccount)?true:false);
				worksheet.setFavoriteString1(worksheet.isFavoriteFlag()?"none":"inline");
				worksheet.setFavoriteString2(worksheet.isFavoriteFlag()?"inline":"none");
				worksheet.setFavoriteClass(worksheet.isFavoriteFlag()?"topfocusLove":"");
				worksheet.setCollectString(worksheet.isCollectFlag()?"已收藏":"收藏");
				worksheet.setCollectString1(worksheet.isCollectFlag()?"none":"inline");
				worksheet.setCollectString2(worksheet.isCollectFlag()?"inline":"none");
				worksheet.setCollectClass(worksheet.isCollectFlag()?"topfocusCollect":"");
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return worksheet;
	}

	public List<Worksheet> getListByType(String type, int start, int size) {
		// TODO Auto-generated method stub
		List<Worksheet> worksheetList = new ArrayList<Worksheet>();
		String sql = "";
		switch(type){
			case "new":
				sql = "SELECT *,(SELECT COUNT(*) FROM worksheet_comment b WHERE topCommentID IS NULL AND a.worksheetID = b.worksheetID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM worksheet_favorite c WHERE a.worksheetID = c.worksheetID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM worksheet_report d WHERE a.worksheetID = d.worksheetID) "
						+ "AS reportCount FROM worksheet a WHERE worksheetStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY createDate DESC LIMIT ?,?";
				break;
			case "hot":
				sql = "SELECT *,(SELECT COUNT(*) FROM worksheet_comment b WHERE topCommentID IS NULL AND a.worksheetID = b.worksheetID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM worksheet_favorite c WHERE a.worksheetID = c.worksheetID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM worksheet_report d WHERE a.worksheetID = d.worksheetID) "
						+ "AS reportCount FROM worksheet a WHERE worksheetStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY worksheetViewCount DESC, createDate DESC LIMIT ?,?";
				break;
			case "com":
				sql = "SELECT *,(SELECT COUNT(*) FROM worksheet_comment b WHERE topCommentID IS NULL AND a.worksheetID = b.worksheetID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM worksheet_favorite c WHERE a.worksheetID = c.worksheetID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM worksheet_report d WHERE a.worksheetID = d.worksheetID) "
						+ "AS reportCount FROM worksheet a WHERE worksheetStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY commentCount DESC, createDate DESC LIMIT ?,?";
				break;
			case "fav":
				sql = "SELECT *,(SELECT COUNT(*) FROM worksheet_comment b WHERE topCommentID IS NULL AND a.worksheetID = b.worksheetID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM worksheet_favorite c WHERE a.worksheetID = c.worksheetID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM worksheet_report d WHERE a.worksheetID = d.worksheetID) "
						+ "AS reportCount FROM worksheet a WHERE worksheetStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY favoriteCount DESC, createDate DESC LIMIT ?,?";
				break;
			default:
				sql = "SELECT *,(SELECT COUNT(*) FROM worksheet_comment b WHERE topCommentID IS NULL AND a.worksheetID = b.worksheetID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM worksheet_favorite c WHERE a.worksheetID = c.worksheetID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM worksheet_report d WHERE a.worksheetID = d.worksheetID) "
						+ "AS reportCount FROM worksheet a WHERE worksheetStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY createDate DESC LIMIT ?,?";
				break;
		}
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, start);
			smt.setInt(2, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Worksheet worksheet = new Worksheet();
				worksheet.setId(rs.getInt("worksheetID"));
				worksheet.setTitle(rs.getString("worksheetTitle"));
				worksheet.setContent(rs.getString("worksheetContent"));
				worksheet.setGist(encodeHtml.getGist(96, worksheet.getContent()));
				worksheet.setCreateDate(rs.getDate("createDate"));
				worksheet.setCreateDateString(dateConversion.ConversionYDM(worksheet.getCreateDate()));
				worksheet.setViewCount(rs.getInt("worksheetViewCount"));
				worksheet.setFavoriteCount(rs.getInt("favoriteCount"));
				worksheet.setCommentCount(rs.getInt("commentCount"));
				worksheetList.add(worksheet);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return worksheetList;
	}

	public List<Worksheet> getListByCategory(String type, int worksheetCategoryID, int start, int size) {
		// TODO Auto-generated method stub
		List<Worksheet> worksheetList = new ArrayList<Worksheet>();
		String sql = "";
		switch(type){
			case "new":
				sql = "SELECT *,(SELECT COUNT(*) FROM worksheet_comment b WHERE topCommentID IS NULL AND a.worksheetID = b.worksheetID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM worksheet_favorite c WHERE a.worksheetID = c.worksheetID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM worksheet_report d WHERE a.worksheetID = d.worksheetID) "
						+ "AS reportCount FROM worksheet a WHERE worksheetCategoryID = ? AND worksheetStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY createDate DESC LIMIT ?,?";
				break;
			case "hot":
				sql = "SELECT *,(SELECT COUNT(*) FROM worksheet_comment b WHERE topCommentID IS NULL AND a.worksheetID = b.worksheetID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM worksheet_favorite c WHERE a.worksheetID = c.worksheetID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM worksheet_report d WHERE a.worksheetID = d.worksheetID) "
						+ "AS reportCount FROM worksheet a WHERE worksheetCategoryID = ? AND worksheetStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY worksheetViewCount DESC, createDate DESC LIMIT ?,?";
				break;
			case "com":
				sql = "SELECT *,(SELECT COUNT(*) FROM worksheet_comment b WHERE topCommentID IS NULL AND a.worksheetID = b.worksheetID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM worksheet_favorite c WHERE a.worksheetID = c.worksheetID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM worksheet_report d WHERE a.worksheetID = d.worksheetID) "
						+ "AS reportCount FROM worksheet a WHERE worksheetCategoryID = ? AND worksheetStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY commentCount DESC, createDate DESC LIMIT ?,?";
				break;
			case "fav":
				sql = "SELECT *,(SELECT COUNT(*) FROM worksheet_comment b WHERE topCommentID IS NULL AND a.worksheetID = b.worksheetID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM worksheet_favorite c WHERE a.worksheetID = c.worksheetID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM worksheet_report d WHERE a.worksheetID = d.worksheetID) "
						+ "AS reportCount FROM worksheet a WHERE worksheetCategoryID = ? AND worksheetStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY favoriteCount DESC, createDate DESC LIMIT ?,?";
				break;
			default:
				sql = "SELECT *,(SELECT COUNT(*) FROM worksheet_comment b WHERE topCommentID IS NULL AND a.worksheetID = b.worksheetID) "
						+ "AS commentCount,(SELECT COUNT(*) FROM worksheet_favorite c WHERE a.worksheetID = c.worksheetID) "
						+ "AS favoriteCount,(SELECT COUNT(*) FROM worksheet_report d WHERE a.worksheetID = d.worksheetID) "
						+ "AS reportCount FROM worksheet a WHERE worksheetCategoryID = ? AND worksheetStatus = 1 HAVING reportCount < 10 "
						+ "ORDER BY createDate DESC LIMIT ?,?";
				break;
		}
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, worksheetCategoryID);
			smt.setInt(2, start);
			smt.setInt(3, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Worksheet worksheet = new Worksheet();
				worksheet.setId(rs.getInt("worksheetID"));
				worksheet.setTitle(rs.getString("worksheetTitle"));
				worksheet.setContent(rs.getString("worksheetContent"));
				worksheet.setGist(encodeHtml.getGist(96, worksheet.getContent()));
				worksheet.setCreateDate(rs.getDate("createDate"));
				worksheet.setCreateDateString(dateConversion.ConversionYDM(worksheet.getCreateDate()));
				worksheet.setViewCount(rs.getInt("worksheetViewCount"));
				worksheet.setFavoriteCount(rs.getInt("favoriteCount"));
				worksheet.setCommentCount(rs.getInt("commentCount"));
				worksheetList.add(worksheet);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return worksheetList;
	}

	@Override
	public List<Worksheet> getListByCategoryTop3(int categoryID, int currentWorksheetID) {
		// TODO Auto-generated method stub
		List<Worksheet> worksheetList = new ArrayList<Worksheet>();		
		String sql = "SELECT *,(SELECT COUNT(*) FROM worksheet_report d WHERE a.worksheetID = d.worksheetID) "
				+ "AS reportCount FROM worksheet a JOIN worksheet_category c ON a.worksheetCategoryID = c.worksheetCategoryID "
				+ "WHERE a.worksheetCategoryID = ? AND worksheetID != ? AND worksheetStatus = 1 HAVING reportCount < 10 "
				+ "ORDER BY createDate DESC LIMIT 3";
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setInt(1, categoryID);
			smt.setInt(2, currentWorksheetID);
			rs = smt.executeQuery();
			while(rs.next()){
				Worksheet worksheet = new Worksheet();
				worksheet.setId(rs.getInt("worksheetID"));
				worksheet.setTitle(rs.getString("worksheetTitle"));
				worksheetList.add(worksheet);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return worksheetList;
	}

	@Override
	public int countTotal(int worksheetCategoryID) {
		// TODO Auto-generated method stub
		int count = 0;
		try {			
			conn = dataSource.getConnection();
			if(worksheetCategoryID != 0){
				String sql = "SELECT *,(SELECT COUNT(*) FROM worksheet_report b WHERE a.worksheetID = b.worksheetID HAVING COUNT(*)<10) "
						+ "AS reportCount FROM worksheet a WHERE worksheetCategoryID = ? AND worksheetStatus = 1 "
						+ "HAVING reportCount IS NOT NULL";	
				smt = conn.prepareStatement(sql);
				smt.setInt(1, worksheetCategoryID);
			}
			else{
				String sql = "SELECT *,(SELECT COUNT(*) FROM worksheet_report b WHERE a.worksheetID = b.worksheetID HAVING COUNT(*)<10) "
						+ "AS reportCount FROM worksheet a WHERE worksheetStatus = 1 HAVING reportCount IS NOT NULL";	
				smt = conn.prepareStatement(sql);
			}
			
			rs = smt.executeQuery();
			rs.beforeFirst();  
			rs.last(); 
			count = rs.getRow();
			
			rs.close();
			smt.close();
			
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}

	@Override
	public List<Worksheet> getListByMember(String account, int start, int size) {
		// TODO Auto-generated method stub
		List<Worksheet> worksheetList = new ArrayList<Worksheet>();
		String sql = "SELECT *,(SELECT COUNT(*) FROM worksheet_comment b WHERE topCommentID IS NULL AND a.worksheetID = b.worksheetID) "
				+ "AS commentCount,(SELECT COUNT(*) FROM worksheet_favorite c WHERE a.worksheetID = c.worksheetID) "
				+ "AS favoriteCount,(SELECT COUNT(*) FROM worksheet_report d WHERE a.worksheetID = d.worksheetID) "
				+ "AS reportCount FROM worksheet a WHERE memberAccount = ? AND worksheetStatus = 1 HAVING reportCount < 10 "
				+ "ORDER BY createDate DESC LIMIT ?,?";
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			smt.setInt(2, start);
			smt.setInt(3, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Worksheet worksheet = new Worksheet();
				worksheet.setId(rs.getInt("worksheetID"));
				worksheet.setTitle(rs.getString("worksheetTitle"));
				worksheet.setContent(rs.getString("worksheetContent"));
				worksheet.setGist(encodeHtml.getGist(96, worksheet.getContent()));
				worksheet.setCreateDate(rs.getDate("createDate"));
				worksheet.setCreateDateString(dateConversion.ConversionYDM(worksheet.getCreateDate()));
				worksheet.setStatus(rs.getInt("worksheetStatus"));
				worksheet.setViewCount(rs.getInt("worksheetViewCount"));
				worksheet.setFavoriteCount(rs.getInt("favoriteCount"));
				worksheet.setCommentCount(rs.getInt("commentCount"));
				worksheetList.add(worksheet);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return worksheetList;
	}

	@Override
	public List<Worksheet> getListByWriter(String account, int start, int size) {
		// TODO Auto-generated method stub
		List<Worksheet> worksheetList = new ArrayList<Worksheet>();
		String sql = "SELECT *,(SELECT COUNT(*) FROM worksheet_comment b WHERE topCommentID IS NULL AND a.worksheetID = b.worksheetID) "
				+ "AS commentCount,(SELECT COUNT(*) FROM worksheet_favorite c WHERE a.worksheetID = c.worksheetID) "
				+ "AS favoriteCount,(SELECT COUNT(*) FROM worksheet_report d WHERE a.worksheetID = d.worksheetID) "
				+ "AS reportCount FROM worksheet a WHERE memberAccount = ? AND worksheetStatus != 3 HAVING reportCount < 10 "
				+ "ORDER BY createDate DESC LIMIT ?,?";
		
		try {
			conn = dataSource.getConnection();
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			smt.setInt(2, start);
			smt.setInt(3, size);
			rs = smt.executeQuery();
			while(rs.next()){
				Worksheet worksheet = new Worksheet();
				worksheet.setId(rs.getInt("worksheetID"));
				worksheet.setTitle(rs.getString("worksheetTitle"));
				worksheet.setContent(rs.getString("worksheetContent"));
				worksheet.setGist(encodeHtml.getGist(96, worksheet.getContent()));
				worksheet.setCreateDate(rs.getDate("createDate"));
				worksheet.setCreateDateString(dateConversion.ConversionYDM(worksheet.getCreateDate()));
				worksheet.setStatus(rs.getInt("worksheetStatus"));
				worksheet.setActString(worksheet.getStatus()==2?"顯示":"隱藏");
				worksheet.setActClass(worksheet.getStatus()==2?"show":"hide");
				worksheet.setHideClass(worksheet.getStatus()==2?"edithide":"");
				worksheet.setViewCount(rs.getInt("worksheetViewCount"));
				worksheet.setFavoriteCount(rs.getInt("favoriteCount"));
				worksheet.setCommentCount(rs.getInt("commentCount"));
				worksheetList.add(worksheet);
			}
			rs.close();
			smt.close();
 
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		
		return worksheetList;
	}

	@Override
	public int countTotalByMember(String account) {
		// TODO Auto-generated method stub
		int count = 0;
		try {			
			conn = dataSource.getConnection();
			String sql = "SELECT *,(SELECT COUNT(*) FROM worksheet_report b WHERE a.worksheetID = b.worksheetID HAVING COUNT(*)<10) "
					+ "AS reportCount FROM worksheet a WHERE memberAccount = ? AND worksheetStatus = 1 "
					+ "HAVING reportCount IS NOT NULL";	
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			rs = smt.executeQuery();
			rs.beforeFirst();  
			rs.last(); 
			count =rs.getRow();
			
			rs.close();
			smt.close();
			
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}

	@Override
	public int countTotalByWriter(String account) {
		// TODO Auto-generated method stub
		int count = 0;
		try {			
			conn = dataSource.getConnection();
			String sql = "SELECT *,(SELECT COUNT(*) FROM worksheet_report b WHERE a.worksheetID = b.worksheetID HAVING COUNT(*)<10) "
					+ "AS reportCount FROM worksheet a WHERE memberAccount = ? AND worksheetStatus != 3 HAVING reportCount IS NOT NULL";	
			smt = conn.prepareStatement(sql);
			smt.setString(1, account);
			rs = smt.executeQuery();
			rs.beforeFirst();  
			rs.last(); 
			count =rs.getRow();
			
			rs.close();
			smt.close();
			
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
 
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {}
			}
		}
		return count;
	}

}
